﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Web.Configuration;
using System.Data.OracleClient;
using System.Collections;

public partial class Suanhua_ExcelImport : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
        {
            //Response.Write("<script>alert('请您选择Excel文件')</script> ");
            //Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script>alert('请您选择Excel文件!');</script>");
            Label5.Text = "请您选择Excel文件!";
            return;//当无文件时,返回
        }
        string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
        if (IsXls != ".xls")
        {
            Label5.Text = "只可以选择Excel文件!";
            return;//当选择的不是Excel文件时,返回
        }
        //string filename = FileUpload1.FileName;//获取Execle文件名  DateTime日期函数
        string filename = System.IO.Path.GetFileNameWithoutExtension(FileUpload1.FileName).ToString().ToLower();
        filename = filename + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
        string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
        
        FileUpload1.SaveAs(savePath);                        //SaveAs 将上传的文件内容保存在服务器上

        ExcelHandler eh = new ExcelHandler();
        DataSet ds = eh.ExcelSqlConnection(savePath, filename);//调用自定义方法
        DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组
        
        int rowsnum = ds.Tables[0].Rows.Count;
        if (rowsnum == 0)
        {
            Label5.Text = "Excel表为空表,无数据!";
        }
        else
        {
            string connStr = WebConfigurationManager.ConnectionStrings["ConnectionStringForOracle"].ConnectionString;
            //string commStr = "insert into bc_cscgjl (jh,jb,cslb,kgrq,wgrq,csmc,sgfs,cscd,bz) values('" + sr.JH + "','" + sr.JB + "','" + "1200',to_date('" + sr.KGRQ + "','yyyy-mm-dd'),to_date('" + sr.WGRQ + "','yyyy-mm-dd'),'" + sr.CSMC + "','" + sr.SGFS + "','" + sr.CSCD + "','" + sr.BZ + "')";

            OracleConnection conn = new OracleConnection(connStr);

            conn.Open();

            try
            {
                int ErrorCount = 0;
                ArrayList Error = new ArrayList();
                int InsertCount = 0;
                ArrayList Insert = new ArrayList();
                int UpdateCount = 0;
                ArrayList Update = new ArrayList();

                for (int i = 0; i < dr.Length; i++)
                {              
                    string JH = dr[i]["井号"].ToString();
                    string JB = dr[i]["井别"].ToString();
                    string KGRQ = Convert.ToDateTime(dr[i]["开工日期"]).ToShortDateString();
                    //DateTime KGRQ = Convert.ToDateTime(dr[i]["开工日期"].ToString());
                    string WGRQ = Convert.ToDateTime(dr[i]["完工日期"]).ToShortDateString();
                    //DateTime WGRQ = Convert.ToDateTime(dr[i]["完工日期"].ToString());
                    string CSMC = dr[i]["措施名称"].ToString();
                    string SGFS = dr[i]["施工方式"].ToString();
                    string CSCD = dr[i]["措施层段"].ToString();
                    decimal SHBJ = decimal.Parse(dr[i]["酸化半径"].ToString());
                    string BZ = dr[i]["备注"].ToString();

                    if (JH != "" && JB != "" && KGRQ.ToString() != "")
                    {
                        OracleCommand selectcmd = new OracleCommand("select count(*) from bc_cscgjl where jh='" + JH + "' and jb='" + JB + "' and kgrq=to_date('" + KGRQ + "','yyyy-mm-dd')", conn);//to_date('" + sr.KGRQ + "','yyyy-mm-dd')
                        int count = Convert.ToInt32(selectcmd.ExecuteScalar());
                        if (count > 0)
                        {
                            UpdateCount++;
                            Update.Add((i+1).ToString());//因为用户习惯从1开始计数，而不是从0开始
                        }
                        else
                        {
                            OracleCommand insertcmd = new OracleCommand("insert into bc_cscgjl(jh,jb,cslb,kgrq,wgrq,csmc,sgfs,cscd,shbj,bz) values('" + JH + "','" + JB + "','1200',to_date('" + KGRQ + "','yyyy-mm-dd'),to_date('" + WGRQ + "','yyyy-mm-dd'),'" + CSMC + "','" + SGFS + "','" + CSCD + "'," + SHBJ + ",'" + BZ + "')", conn);
                            insertcmd.ExecuteNonQuery();
                            InsertCount++;
                            Insert.Add((i+1).ToString());
                        }
                    }
                    else
                    {
                        ErrorCount++;//某个唯一约束字段为空
                        Error.Add((i+1).ToString());
                    }
                }
                Label5.Text = InsertCount.ToString() + "条数据导入成功!" + UpdateCount.ToString() + "条数据重复!" + ErrorCount.ToString() + "条数据部分信息为空没有导入!";
                //重置label 6～7
                Label6.Text = "插入成功的行：";
                Label7.Text = "数据重复的行：";
                Label8.Text = "插入失败的行：";
                foreach (string s in Insert)
                {
                    Label6.Text += " " + s;
                }
                foreach (string s in Update)
                {
                    Label7.Text += " " + s;
                }
                foreach (string s in Error)
                {
                    Label8.Text += " " + s;
                }
            }
            catch (Exception ex)
            {
                Label5.Text = ex.Message;
            }
            finally
            {
                conn.Close();
            }            
        }
    }
}